1
The Performance Investigation
DSAI2202Lab 8: Composing Data Operations

The academic support team needs a list of active enrolments whose recent performance has declined. Compare each student with their own earlier performance in the same course.

Agree on the Rules

  1. Use two periods: earlier and recent. Each has two equally weighted assessments.
  2. Require at least two non-NULL scores in each period before comparing averages.
  3. Calculate decline_points = earlier_avg - recent_avg.
  4. A decline of at least 15 points means Review. Otherwise an eligible enrolment has No alert.
  5. Keep Insufficient data separate. A zero is a valid score; a NULL or missing record is not a zero.

Prepare Your Database

Open the supplied lab8_setup.sql from the TA folder DSAI2202/lab8/ in pgAdmin and run the complete file. Use the connection setting on the right again whenever you open a new connection.

The setup also provides performance_comparison, a saved query called a view. It will support later exercises; it is not an extra table of stored assessment results.

✍️ Inspect Before Calculating
Inspect E01, E07, E08, E09, and E24. Decide which have enough scores in both periods. Explain why E24's zeros provide stronger evidence than E08's NULL values.
Set schema
SET search_path TO lab8;
Check counts
SET search_path TO lab8;

SELECT 'students' AS table_name, COUNT(*) AS row_count
FROM students
UNION ALL SELECT 'courses', COUNT(*) FROM courses
UNION ALL SELECT 'enrolments', COUNT(*) FROM enrolments
UNION ALL SELECT 'assessment_results', COUNT(*) FROM assessment_results
UNION ALL SELECT 'support_units', COUNT(*) FROM support_units
ORDER BY table_name;
Inspect evidence
SET search_path TO lab8;

SELECT e.enrolment_id, e.student_id, e.course_code,
       r.result_id, r.period, r.assessment_no, r.score
FROM enrolments AS e
JOIN assessment_results AS r
  ON r.enrolment_id = e.enrolment_id
WHERE e.enrolment_id IN ('E01', 'E07', 'E08', 'E09', 'E24')
ORDER BY e.enrolment_id, r.period, r.assessment_no;